// merge baseball ratings and CLI
import delimited "build/input/baseball/cli_by_game_nationally_televised_pseudo.csv", clear varnames(1)
gen date2 = date(date, "MDY", 2050)
format date2 %td
drop date
rename date2 date
tempfile cli
save `cli', replace

import delimited "build/input/baseball/ratings_pseudo.csv", clear varnames(1)
gen date2 = date(date, "MDY", 2050)
format date2 %td
drop date
rename date2 date
merge 1:1 date firstteam secondteam using `cli', nogen

collapse (mean) cli householdrating, by(date)
gen HouseholdRating_times_cli = householdrating*cli

tempfile baseball
save `baseball'

// merge main datasets
use "build/output/crypto_daily_panel.dta", clear
merge 1:1 coin date using "build/output/lending_rates_wide.dta", nogen
merge m:1 date using "build/output/implied_vol.dta", nogen
merge m:1 date using "build/output/bbg_crypto_futures.dta", nogen
merge m:1 date using "build/output/futures_funding_avg.dta", nogen
merge m:1 date using `baseball', nogen
merge 1:1 coin date using "build/output/intraday_stablecoin_prices.dta", nogen

// identify baseball data; FTX began sponsorship on july 13 2021: https://www.mlb.com/news/mlb-ftx-cryptocurrency-exchange-partnership
gen baseball_season = 0
replace baseball_season = 1 if (date>=mdy(7,13,2021) & date <=mdy(11,2,2021)) | (date>=mdy(4,7,2022) & date <=mdy(11,5,2022))

// coins of interest
keep if inlist(coin, "USDT", "DAI")

// calculate price deviation
gen dev = 1-p

// put into same units
replace r_btc = r_btc*100
replace r_eth = r_eth*100

// clean btc and eth, which are available only for business week
rename r_btc r_btc_bbg
rename r_eth r_eth_bbg

// merge in coingecko's 24 hour btc and eth return
merge m:1 date using "build/output/coingecko_btc_return.dta", nogen
merge m:1 date using "build/output/coingecko_eth_return.dta", nogen
merge m:1 date using "build/output/coingecko_doge_return.dta", nogen

// merge fed funds
preserve
import excel "build/input/fred/EFFR.xls", sheet("to_stata") firstrow clear
tempfile effr
save `effr'
restore
merge m:1 date using `effr', nogen

// create business calendar date, only used for traditional financial markets
preserve
keep date y_effr
drop if missing(y_effr)
bcal create y_effr, from(date) replace personal 
restore

bcal load y_effr
generate bcaldate = bofd("y_effr",date)
format %tby_effr bcaldate

egen coin_id = group(coin)

gen mdate = mofd(date)
format mdate %tm

gen qdate = qofd(date)
format qdate %tq

gen wdate = wofd(date)
gen dow = dow(date)
gen year = year(date)
gen month=month(date)

// set date
drop if date < mdy(12,1,2020)
drop if date > mdy(11,5,2022)

tsset coin_id date
order date coin

save "build/output/main_panel.dta", replace
